﻿
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapICC_JWYARN(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_JWYARN(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryICC_JWYARN(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapICC_JWYARN(ref dwLocal, dwData);

            if (!flgExist)
            {
                dwLocal["ROWNID"] = GetSeq();
                InsertICC_JWYARN(dwLocal, 1);
            }
            UpdateICC_JWYARN(dwLocal);
        }
        public void InsertICC_JWYARN(DataRow dwData, int option = 1)
        {

            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "INSERT INTO ICC_JWYARN  SELECT DISTINCT 'J ' AS JWS,BAGART AS WART,BAGFARBE AS WFARBE,BAGGART AS GART,BAGGVAR AS GVAR, BAGGFARBE AS GFARBE,BAGKGBRUT AS GUSAGE,0 AS CR,0 AS CG,0 AS CB,0 AS RGB,0 AS CGROUP    FROM BASTGA JOIN ICC_PVKPOS P ON VAFPART=BAGART AND BAGFARBE=VAFFFARBE AND VKPEND=0   WHERE NOT EXISTS ( SELECT 1 FROM ICC_JWYARN X WHERE JWS='J '    AND X.WART=BAGART AND BAGFARBE=WFARBE AND BAGGART=GART AND BAGGVAR = GVAR AND BAGGFARBE = GFARBE  )";
                    break;
                case 2:
                    or1.CommandText = "INSERT INTO ICC_JWYARN   SELECT DISTINCT 'W ' AS JWS,WAGART AS WART,WAGWCOL AS WFARBE,WAGGART AS GART,WAGGVAR AS GVAR,WAGGFARBE AS GFARBE,WAGKGBRUT AS GUSAGE,0 AS CR,    0 AS CG,0 AS CB,0 AS RGB,0 AS CGROUP FROM WASTGA JOIN ICC_PVKPOS P ON VAFPART=WAGART AND WAGWCOL=VAFFFARBE AND VKPEND=0       WHERE NOT EXISTS   ( SELECT 1 FROM ICC_JWYARN X WHERE JWS='W ' AND X.WART=WAGART AND WAGWCOL=WFARBE AND WAGGART=GART AND WAGGVAR = GVAR AND WAGGFARBE = GFARBE   )";
                    break;
                case 3:
                    or1.CommandText = "INSERT INTO ICC_JWYARN   SELECT DISTINCT  'J ' AS JWS,GAGART AS WART,GAGFARBE AS WFARBE,GAGGART AS GART,GAGGVAR AS GVAR,GAGGFARBE AS GFARBE,GAGKGBRUT AS GUSAGE,0 AS CR,    0 AS CG,0 AS CB,0 AS RGB,0 AS CGROUP FROM GASTGA JOIN ICC_PVKPOS P ON VAFPART=GAGART AND GAGFARBE=VAFFFARBE AND VKPEND=0       WHERE NOT EXISTS   ( SELECT 1 FROM ICC_JWYARN X WHERE JWS='J ' AND X.WART=GAGART AND GAGFARBE=WFARBE AND GAGGART=GART AND GAGGVAR = GVAR AND GAGGFARBE = GFARBE   )";
                    break;
                case 4:
                    or1.CommandText = "INSERT INTO ICC_JWYARN   SELECT DISTINCT  'W ' AS JWS,GAGART AS WART,GAGFARBE AS WFARBE, GAGART AS GART,GAGVAR AS GVAR,GAGFARBE AS GFARBE,'0010000000' AS GUSAGE,0 AS CR,    0 AS CG,    0 AS CB,0 AS RGB,0 AS CGROUP     FROM GASTGA      JOIN ICC_PVKPOS P ON VAFPART=GAGART AND GAGFARBE=VAFFFARBE AND VKPEND=0      WHERE NOT EXISTS      ( SELECT 1 FROM ICC_JWYARN X      WHERE JWS='W ' AND X.WART=GAGART AND WFARBE=GAGFARBE  AND X.GART=GAGART AND GFARBE=GAGFARBE  )";
                    break; 
                default:
                    break;
            }

            Connect2Proxy(or1);
        }
        public DataSet QueryICC_JWYARN(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT * FROM ICC_JWYARN " ; 
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM ICC_JWYARN JOIN ICC_PVKPOS ON WART=VAFPART AND WFARBE=VAFFFARBE"
                        + " WHERE VKPMANDANT=:VKPMANDANT AND VKPEND=0";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    break; 
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_JWYARN";
            Return = Result;
            return Return;
        }
        public void UpdateICC_JWYARN(DataRow dwData, int option=1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            { 
                case 1:
                    break;
               case 2:
                    //or1.CommandText = "UPDATE ICC_JWYARN set CR=ROUND(dbms_random.value(0,255),0) , CG=ROUND(dbms_random.value(0,255),0),CB=ROUND(dbms_random.value(0,255),0),CGROUP=ROUND(dbms_random.value(0,9),0) ,RGB=1 WHERE RGB=0";
                    or1.CommandText="UPDATE ICC_JWYARN X set (CR,CG,CB,CGROUP,RGB)=(SELECT SUBSTR(SDDATEN,42,3),SUBSTR(SDDATEN,45,3),SUBSTR(SDDATEN,48,3),SUBSTR(SDDATEN,52,1),1  FROM SDDAT WHERE SDSA='0014' AND  SDKEY ='00'||GFARBE||'           V')  WHERE RGB=0 AND EXISTS(SELECT 1 FROM SDDAT WHERE SDSA='0014' AND  SDKEY ='00'||GFARBE||'           V')";
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet DeleteICC_JWYARN(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_JWYARN WHERE  ABAID=:ABAID"; ;
                    or1.Parameters.Add("ABAID", OracleType.NVarChar).Value = dwData["ABAID"].ToString();
                    break;
                case 2://WART,WFARBE
                    or1.CommandText = "DELETE FROM ICC_JWYARN WHERE  WART=:WART AND WFARBE=:WFARBE"; ;
                    or1.Parameters.Add("WART", OracleType.NVarChar).Value = dwData["WAFART"].ToString();
                    or1.Parameters.Add("WFARBE", OracleType.NVarChar).Value = dwData["WAFZUSATZ"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
            return Result;
        }
    }
}
